using LittleBee.DBA;
using MySql.Data.MySqlClient;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;

namespace LittleBee.IDAL
{
	public interface ILBAccess
	{
		bool QueryCellIDs(out Int64[]? cell_ids, bool blEnable = true);
		bool QueryCellTypeList(out DataSet? dsCellInfos, bool blEnable = true);
		bool QueryCellTypeInfo(string cellType, out DataSet? dsCellInfo);

		bool QuerySystemCellIDs(out Int64[]? cell_ids, bool blEnable = true);
		bool QuerySystemCellInfos(out DataSet? dsCellInfos, bool blEnable = true);

		//bool InsertCellInfo(out Int64 nid, Dictionary<string, object> dctCellInfo, string outputField);
		bool CreateCellApp(Int64 cell_id, out DataTable? dtNewApp);
		bool CreateCellApps(Int64 cell_id, int count, bool blErrBreak, out DataTable? dtNewApp, out string? errMsg);

		bool QueryCellAppsInfo(out DataSet? dsAppInfos, bool blEnable = true);

		bool QueryCellAppList(string strCellType, out DataSet? dsAppInfos, bool blEnable = true);

		bool QueryCellAppList(Int64 nCellID, out DataSet? dsAppInfos, bool blEnable = true);

		bool RemoveFirstLoaded(Int64 instanceID);

		bool IsFirstLoaded(Int64 instanceID);
		bool QueryAppPointers(Int64 instanceID, out DataTable? dtPointers);
		bool InsertCellPointers(Int64 nCellID, string strFieldNames, string[] Pointers);
		bool CheckDbExist(string v);
		bool CreateDatabase(string strDbName);
		bool StoreQueryStoreList(out DataTable? dtStores);
		bool StoreQueryCellList(Int64 cell_id, out DataTable? dtCells);
		bool StoreQueryCellInfo(Int64 cellID, out DataTable? dtCellInfo);
		bool IsStoreExist(string strStoreName);
		bool StoreCreateStore(string strStoreName, string strStoreDesc, out Int64 store_id);
		bool IsStoreCellExist(Int64 nStoreID, string strCellType);
		bool StoreCreateCell(Int64 nStoreID, string strCellType, string strCellName, string strCellDesc, string strCellClass, string strCellCode, out Int64 strCell_ID);
		bool StoreUpdateCellPointers(DataTable dataTableCellPointers);
		bool StoreUpdateCellInfo(DataTable dataTableCellInfo);
	}

	public class LBAccess : ILBAccess
	{
		private static readonly LBAccess _oSingleton = new();

		const string strIotDBConnection = "server=127.0.0.1;User ID=root;password=zhangjf123;Database=lbiot;";
		const string strUserDBConnection = "server=127.0.0.1;User ID=root;password=zhangjf123;Database=lbiotuser;";
		const string strStoreDBConnection = "server=127.0.0.1;User ID=root;password=zhangjf123;Database=lbiotstore;";
		DBAccess _iotdba = new MySqlAccess(strIotDBConnection);
		DBAccess _userdba = new MySqlAccess(strUserDBConnection);
		DBAccess _storedba = new MySqlAccess(strStoreDBConnection);

		private LBAccess()
		{
			//Initialize();
			//_iotdba = new MySqlAccess(strIotDBConnection);
			//_userdba = new MySqlAccess(strUserDBConnection);
			//_storedba = new MySqlAccess(strStoreDBConnection);
			CreateAndUpdateIotDatabase();
			CreateAndUpdateIotStoreDatabase();
		}

		public static LBAccess Instance
		{
			get
			{
				return _oSingleton;
			}
		}

//		private void Initialize()
//		{
//		}

		private void CreateAndUpdateIotDatabase()
		{
			if (CheckDbExist("lbiot") == false)
			{
				CreateDatabase("lbiot");
				ExecuteScriptFile("lbiot", "lbiot.sql");
			}
			else
			{
				if (CheckTableExist("lbiot", "tblObj_Id") == false
					|| CheckTableExist("lbiot", "tblCell_Info") == false
					|| CheckTableExist("lbiot", "tblCell_Pointer") == false
					|| CheckTableExist("lbiot", "tblApp_Info") == false
					|| CheckTableExist("lbiot", "tblApp_Pointer") == false)
				{
					ExecuteScriptFile("lbiot", "lbiot.sql");
				}
			}
		}

		private void CreateAndUpdateIotStoreDatabase()
		{
			if (CheckDbExist("lbiotstore") == false)
			{
				CreateDatabase("lbiotstore");
				ExecuteScriptFile("lbiotstore", "lbiotstore.sql");
			}
			else
			{
				if (CheckTableExist("lbiotstore", "tblStore") == false
					|| CheckTableExist("lbiotstore", "tblCell_Info") == false
					|| CheckTableExist("lbiotstore", "tblCell_Pointer") == false)
				{
					ExecuteScriptFile("lbiotstore", "lbiotstore.sql");
				}
			}
		}

		public bool QueryCellAppsInfo(out DataSet? dsAppsInfo, bool blEnable = true)
		{
			dsAppsInfo = null;
			bool blRet = false;

			string strQuery;
			if (blEnable == true)
				strQuery = $"SELECT * FROM tblApp_Info WHERE enable = 1;";
			else
				strQuery = $"SELECT * FROM tblApp_Info WHERE enable = 0;";

			try
			{
				dsAppsInfo = _iotdba.ExecuteDataSet(strQuery);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool QueryCellTypeAppsList(string strCellType, out DataSet? dsAppsInfo, bool blEnable = true)
		{
			dsAppsInfo = null;
			bool blRet = false;

			string strQuery;
			if (blEnable == true)
				strQuery = $"SELECT * FROM tblApp_Info WHERE enable = 1;";
			else
				strQuery = $"SELECT * FROM tblApp_Info WHERE enable = 0;";

			try
			{
				dsAppsInfo = _iotdba.ExecuteDataSet(strQuery);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool IsCellTypeExist(string strCellType, string strCellUUID, out Int64 cell_id)
		{
			bool blRet = false;
			cell_id = 0;

			string strQuery = $"SELECT cell_id FROM tblCell_Info WHERE type = '{strCellType}' AND cell_uuid = '{strCellUUID}';";

			try
			{
				object o = _iotdba.ExecuteScalar(strQuery);
				if (o != null)
				{
					cell_id = (Int64)o;
					blRet = true;
				}

			}
			catch (Exception ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool IsFirstLoaded(Int64 instanceID)
		{
			bool blRet = false;

			string strQuery = $"SELECT first_loaded FROM tblObj_ID WHERE obj_id ={instanceID}";

			try
			{
				DataSet dsQuery = _iotdba.ExecuteDataSet(strQuery);

				if (dsQuery.Tables[0].Rows.Count == 1 && dsQuery.Tables[0].Rows[0].Field<UInt64>("first_loaded") == 1)
				{
					blRet = true;
				}
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool RemoveFirstLoaded(Int64 instanceID)
		{
			bool blRet = false;
			
			string strQuery = $"UPDATE tblObj_ID SET first_loaded = 0 WHERE obj_id ={instanceID}";

			try
			{
				_iotdba.ExecuteNonquery(strQuery);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool QueryCellIDs(out Int64[]? cell_ids, bool blEnable = true)
		{
			cell_ids = null;
			bool blRet = false;

			string strQuery;
			if (blEnable == true)
				strQuery = $"SELECT cell_id FROM tblCell_Info WHERE enable = 1;";
			else
				strQuery = $"SELECT cell_id FROM tblCell_Info WHERE enable = 0;";

			try
			{
				DataSet ds = _iotdba.ExecuteDataSet(strQuery);

				List<Int64> cids = new List<Int64>();
				foreach (DataRow item in ds.Tables[0].Rows)
				{
					cids.Add(item.Field<Int64>(0));
				}

				cell_ids = cids.ToArray();
				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool QueryCellTypeInfo(string cellType, out DataSet? dsCellInfo)
		{
			dsCellInfo = null;
			bool blRet = false;

			string strQuery;
			strQuery = $"SELECT * FROM tblCell_Info WHERE type = '{cellType}';";

			try
			{
				dsCellInfo = _iotdba.ExecuteDataSet(strQuery);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool QueryCellTypeList(out DataSet? dsCellInfos, bool blEnable = true)
		{
			dsCellInfos = null;
			bool blRet = false;

			string strQuery;
			if (blEnable == true)
				strQuery = $"SELECT * FROM tblCell_Info WHERE enable = 1;";
			else
				strQuery = $"SELECT * FROM tblCell_Info WHERE enable = 0;";

			try
			{
				dsCellInfos = _iotdba.ExecuteDataSet(strQuery);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool QuerySystemCellIDs(out Int64[]? cell_ids, bool blEnable = true)
		{
			bool blRet = false;

			string strQuery;
			if (blEnable == true)
				strQuery = $"SELECT cell_id FROM tblCell_Info WHERE enable = 1;";
			else
				strQuery = $"SELECT cell_id FROM tblCell_Info WHERE enable = 0;";

			try
			{
				DataSet ds = _iotdba.ExecuteDataSet(strQuery);

				List<Int64> cids = new List<Int64>();
				foreach (DataRow item in ds.Tables[0].Rows)
				{
					cids.Add(item.Field<Int64>(0));
				}

				cell_ids = cids.ToArray();
				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
				cell_ids = null;
			}

			return blRet;
		}

		public bool UpdateCellPointerValue(Int64 appID, string pid, string v)
		{
			bool blRet;
			try
			{
				string strQuery = $@"UPDATE tblApp_Pointer as ap SET 
					ap.value = '{v}'
					WHERE ap.app_id = {appID} AND ap.pid = '{pid}';
				";
				int nRow = _iotdba.ExecuteNonquery(strQuery);

				blRet = true;
			}
			catch (MySqlException ex)
			{
				Console.WriteLine($"更新Cell Pointer value失败： {ex.Message}");
				blRet = false;
			}

			return blRet;
		}

		public bool UpdateCellPointerValueHelper(Int64 appID, string pid, string v)
		{
			bool blRet;
			try
			{
				string strQuery = $@"UPDATE tblApp_Pointer as ap SET 
					ap.helper = '{v}'
					WHERE ap.app_id = {appID} AND ap.pid = '{pid}';
				";
				int nRow = _iotdba.ExecuteNonquery(strQuery);

				blRet = true;
			}
			catch (MySqlException ex)
			{
				Console.WriteLine($"更新Cell Pointer value失败： {ex.Message}");
				blRet = false;
			}

			return blRet;
		}

		public bool CheckTableExist(string strDbName, string strTableName)
		{
			bool blRet = false;
			try
			{
				if (strDbName.Equals("lbiotstore", StringComparison.OrdinalIgnoreCase) == true)
				{
					return _storedba.CheckTableExist(strTableName);
				}
				else if (strDbName.Equals("lbuserdba", StringComparison.OrdinalIgnoreCase) == true)
				{
					return _userdba.CheckTableExist(strTableName);
				}
				else if (strDbName.Equals("lbiot", StringComparison.OrdinalIgnoreCase) == true)
				{
					return _iotdba.CheckTableExist(strTableName);
				}
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool ExecuteScriptFile(string strDbName, string strScriptName)
		{
			bool blRet = false;
			try
			{
				if (strDbName.Equals("lbiotstore", StringComparison.OrdinalIgnoreCase) == true)
				{
					return _storedba.ExecuteScriptFile(strScriptName);
				}
				else if (strDbName.Equals("lbuserdba", StringComparison.OrdinalIgnoreCase) == true)
				{
					return _userdba.ExecuteScriptFile(strScriptName);
				}
				else if (strDbName.Equals("lbiot", StringComparison.OrdinalIgnoreCase) == true)
				{
					return _iotdba.ExecuteScriptFile(strScriptName);
				}
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool IsStoreExist(string strStoreName)
		{
			bool blRet = false;

			try
			{
				string strQuery = $"SELECT COUNT(*) FROM tblStore WHERE name = '{strStoreName}'";
				blRet = Convert.ToInt32(_storedba.ExecuteScalar(strQuery)) > 0;

			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool IsStoreCellExist(Int64 nStoreID, string strCellType)
		{
			bool blRet = false;

			try
			{
				string strQuery = $"SELECT count(*) FROM tblCell_Info WHERE store_id = '{nStoreID}' AND type = '{strCellType}'";
				blRet = Convert.ToInt32(_storedba.ExecuteScalar(strQuery)) > 0;

			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool QuerySystemCellInfos(out DataSet? dsCellInfos, bool blEnable = true)
		{
			dsCellInfos = null;
			bool blRet = false;

			string strQuery;
			if (blEnable == true)
				strQuery = $"SELECT * FROM tblCell_Info WHERE enable = 1;";
			else
				strQuery = $"SELECT * FROM tblCell_Info WHERE enable = 0;";

			try
			{
				dsCellInfos = _iotdba.ExecuteDataSet(strQuery);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool InsertCellInfo(out Int64 nid, Dictionary<string, object> dctCellInfo)
		{
			bool blRet = false;
			nid = 0;

			try
			{
				List<MySqlParameter> para = new List<MySqlParameter>();

				foreach (var item in dctCellInfo)
				{
					para.Add(new MySqlParameter($"@{item.Key}", dctCellInfo[item.Key]));
				};

				int idx = para.FindIndex(x => { return x.ParameterName == "@cell_id"; });
				para[idx].Direction = ParameterDirection.Output;

				int nret = _iotdba.ExecuteProcNonQuery("InsertCell", para.ToArray());

				nid = (Int64)para[idx].Value;

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool StoreCreateStore(string strStoreName, string strStoreDesc, out Int64 store_id)
		{
			bool blRet = false;
			store_id = 0;

			try
			{
				string strQuery = $"INSERT INTO tblStore VALUES(0, REPLACE(UUID(),'-',''), '{strStoreName}', '{strStoreDesc}');";
				int nRow = _storedba.ExecuteNonquery(strQuery);

				if (nRow == 1)
				{
					strQuery = $"SELECT store_id FROM tblStore WHERE name = '{strStoreName}';";
					store_id = (Int64)_storedba.ExecuteScalar(strQuery);
					blRet = true;
				}
			}
			catch (MySqlException ex)
			{
				Console.WriteLine($"创建Cell仓库失败： {ex.Message}");
				blRet = false;
			}

			return blRet;
		}

		public bool CheckCellTypeExist(string cell_uuid, string cell_type, out Int64 cell_id)
		{
			cell_id = 0;

			bool blRet = false;
			try
			{
				string strQuery = $"SELECT cell_id FROM tblCell_Info WHERE cell_uuid = '{cell_uuid}' and type = '{cell_type}'";
				cell_id = (Int64)_iotdba.ExecuteScalar(strQuery);
				blRet = true;
			}
			catch (MySqlException ex)
			{
				Console.WriteLine($"Mysql query executed failed： {ex.Message}");
			}
			catch (NullReferenceException)
			{
				Console.WriteLine($"Not found the specified cell type: {cell_uuid}:{cell_type}");
			}

			return blRet;
		}

		/*public bool RemoveCellApps(Int64 cell_id, out JArray? jaRemovedAppIDs)
		{
			//removed_app_ids = null;
			jaRemovedAppIDs = null;

			if (QueryCellAppIDs(cell_id, out Int64[]? app_ids) == true)
			{
				return RemoveApps(app_ids, out jaRemovedAppIDs);
			}

			return false;
		}

		private bool RemoveApps(Int64[] app_ids, out JArray jaRemovedAppIDs)
		{
			bool blRet = true;
			//jaRemovedAppIDs = null;

			List<Int64> lstRemoved = new List<Int64>();

			foreach (var app_id in app_ids)
			{
				JObject joRemovedApp = new JObject
				{
					["app_id"] = app_id
				};

				if (RemoveCellApp(app_id, out string? strError) == true)
				{
					lstRemoved.Add(app_id);
					joRemovedApp["result"] = true;
				}
				else
				{
					joRemovedApp["result"] = false;
					joRemovedApp["error_msg"] = strError;
					blRet = false;
				}
			}
			jaRemovedAppIDs = JArray.FromObject(lstRemoved.ToArray());

			return blRet;
		}*/

		public bool RemoveCellApp(Int64 app_id, out string? strError)
		{
			bool blRet = false;
			strError = null;

			try
			{
				List<MySqlParameter> para = new List<MySqlParameter>
				{
					new MySqlParameter($"@app_id", app_id),
					new MySqlParameter($"@err_msg", strError)
				};

				//int idx = para.FindIndex(x => { return x.ParameterName == "@" + outputField; });
				para[1].Direction = ParameterDirection.Output;

				int nret = _iotdba.ExecuteProcNonQuery("RemoveApp", para.ToArray());

				strError = (string)para[1].Value;

				blRet = true;
			}
			catch (Exception ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		private bool QueryCellAppIDs(Int64 cell_id, out Int64[] app_ids)
		{
			bool blRet = false;
			app_ids = Array.Empty<Int64>();

			string strQuery = $"SELECT app_id FROM tblApp_Info WHERE cell_id in (SELECT cell_id FROM tblCell_Info WHERE cell_id = {cell_id})";

			DataSet dsAppIDs;
			try
			{
				dsAppIDs = _iotdba.ExecuteDataSet(strQuery);
				List<Int64> lstAppIDs = new List<Int64>();
				foreach (DataRow item in dsAppIDs.Tables[0].Rows)
				{
					lstAppIDs.Add(item.Field<Int64>("app_id"));
				}

				app_ids = lstAppIDs.ToArray();

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool RemoveCellType(Int64 cell_id, string cell_uuid, out string err_msg)
		{
			bool blRet = false;
			err_msg = "";

			try
			{
				List<MySqlParameter> para = new List<MySqlParameter>
				{
					new MySqlParameter($"@cellid", cell_id),
					//new MySqlParameter($"@cell_uuid", cell_uuid),
					new MySqlParameter($"@err_msg", err_msg)
				};

				//int idx = para.FindIndex(x => { return x.ParameterName == "@" + outputField; });
				para[1].Direction = ParameterDirection.Output;

				int nret = _iotdba.ExecuteProcNonQuery("RemoveCell", para.ToArray());

				if (para[1].Value != null)
					err_msg = (string)para[1].Value;

				blRet = true;
			}
			catch (Exception ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool StoreCreateCell(Int64 nStoreID, string strCellType, string strCellName, string strCellDesc, string strCellClass, string strCellCode, out Int64 nCell_ID)
		{
			bool blRet = false;
			nCell_ID = 0;

			try
			{
				StringBuilder sb = new();
				string strQuery = sb.Append($"INSERT INTO tblCell_Info VALUES(")
					.Append($"0, ")							//cell_id
					.Append($"{nStoreID}, ")					//store_id
					.Append($"REPLACE(UUID(),'-',''), ")		//cell_uuid
					.Append($"'{strCellType}', ")			//type
					.Append($"'{strCellName}', ")			//name
					.Append($"'{strCellDesc}', ")			//description
					.Append($"'{strCellClass}', ")			//class
					.Append($"'[]', ")						//attributes
					.Append($"'{{}}', ")						//content
					.Append($"'[]', ")						//categories
					.Append($"'{{}}', ")						//helper
					.Append($"'{{}}', ")						//revisions
					.Append($"'{{}}', ")						//comm_set
					.Append($"'{{}}', ")						//transfer_set
					.Append($"'[]', ")						//groups
					.Append($"'{{}}', ")						//group_set
					.Append($"'[]', ")						//conditions
					.Append($"'{{}}', ")						//condition_set
					.Append($"'[]', ")						//structs
					.Append($"'{{}}', ")						//struct_set
					.Append($"'[]', ")						//io_pointers
					.Append($"'{{}}', ")                        //io_set
					.Append($"'library',")					//code_or_library
					.Append($"'{strCellCode}',")            //code
					.Append($"'');")						//library
					.ToString();

				int nRow = _storedba.ExecuteNonquery(strQuery);

				if (nRow == 1)
				{
					strQuery = $"SELECT cell_id FROM tblCell_Info WHERE type = '{strCellType}' AND store_id = '{nStoreID}';";
					nCell_ID = Convert.ToInt64(_storedba.ExecuteScalar(strQuery));

					blRet = true;
				}
			}
			catch (MySqlException ex)
			{
				Console.WriteLine($"创建Cell失败： {ex.Message}");
				blRet = false;
			}

			return blRet;
		}

		public bool StoreUpdateCellPointers(DataTable dataTableCellPointers)
		{
			bool blRet;
			try
			{
				DataView deleteView = new DataView(dataTableCellPointers, null, null, DataViewRowState.Deleted);
				DataTable dtDeleted = deleteView.ToTable();

				string strQuery;

				foreach (DataRow item in dtDeleted.Rows)
				{
					strQuery = $@"DELETE FROM tblCell_Pointer WHERE pid = '{item.Field<string>("pid")}' AND cell_id = {item.Field<Int64>("cell_id")}";
					int nRow = _storedba.ExecuteNonquery(strQuery);
				}

				DataTable? dtAdded = dataTableCellPointers.GetChanges(DataRowState.Added);
				if (dtAdded != null)
				{
					foreach (DataRow item in dtAdded.Rows)
					{
						strQuery = $@"INSERT INTO tblCell_Pointer(cell_id, pid, name, description, attribute, type, value, helper) VALUES
							({item.Field<Int64>("cell_id")},
							'{item.Field<string>("pid")}',
							'{item.Field<string>("name")}',
							'{item.Field<string>("description")}',
							{item.Field<ulong>("attribute")},
							'{item.Field<string>("type")}',
							'{item.Field<string>("value")}',
							'{item.Field<string>("helper")}');";

						int nRow = _storedba.ExecuteNonquery(strQuery);
					}
				}

				DataTable? dtModified = dataTableCellPointers.GetChanges(DataRowState.Modified);
				if (dtModified != null)
				{
					foreach (DataRow item in dtModified.Rows)
					{
						strQuery = $@"UPDATE tblCell_Pointer SET
						name = '{item.Field<string>("name")}',
						description = '{item.Field<string>("description")}',
						attribute = {item.Field<ulong>("attribute")},
						type = '{item.Field<string>("type")}',
						value = '{item.Field<string>("value")}',
						helper = '{item.Field<string>("helper")}'
						WHERE pid = '{item.Field<string>("pid")}' AND cell_id = {item.Field<Int64>("cell_id")};";

						int nRow = _storedba.ExecuteNonquery(strQuery);
					}
				}

				dataTableCellPointers.AcceptChanges();

				blRet = true;
			}
			catch (MySqlException ex)
			{
				Console.WriteLine($"更新Cell失败： {ex.Message}");
				blRet = false;
			}

			return blRet;
		}

		public bool StoreUpdateCellInfo(DataTable dtCellInfo)
		{
			bool blRet;
			try
			{
				string strQuery = $@"UPDATE tblCell_Info as ci SET 
					ci.name = '{dtCellInfo.Rows[0].Field<string>("name")}',
					ci.description = '{dtCellInfo.Rows[0].Field<string>("description")}',
					ci.class = '{dtCellInfo.Rows[0].Field<string>("class")}',
					ci.attributes = '{dtCellInfo.Rows[0].Field<string>("attributes")}',
					ci.content = '{dtCellInfo.Rows[0].Field<string>("content")}',
					ci.categories = '{dtCellInfo.Rows[0].Field<string>("categories")}',
					ci.helper = '{dtCellInfo.Rows[0].Field<string>("helper")}',
					ci.revisions = '{dtCellInfo.Rows[0].Field<string>("revisions")}',
					ci.comm_set = '{dtCellInfo.Rows[0].Field<string>("comm_set")}',
					ci.transfer_set = '{dtCellInfo.Rows[0].Field<string>("transfer_set")}',
					ci.groups = '{dtCellInfo.Rows[0].Field<string>("groups")}',
					ci.group_set = '{dtCellInfo.Rows[0].Field<string>("group_set")}',
					ci.conditions = '{dtCellInfo.Rows[0].Field<string>("conditions")}',
					ci.condition_set = '{dtCellInfo.Rows[0].Field<string>("condition_set")}',
					ci.structs = '{dtCellInfo.Rows[0].Field<string>("structs")}',
					ci.struct_set = '{dtCellInfo.Rows[0].Field<string>("struct_set")}',
					ci.io_set = '{dtCellInfo.Rows[0].Field<string>("io_set")}',
					ci.io_pointers = '{dtCellInfo.Rows[0].Field<string>("io_pointers")}',
					ci.code_or_library = '{dtCellInfo.Rows[0].Field<string>("code_or_library")}',
					ci.code = '{dtCellInfo.Rows[0].Field<string>("code")}',
					ci.library = '{dtCellInfo.Rows[0].Field<string>("library")}'
					where ci.cell_id = {dtCellInfo.Rows[0].Field<Int64>("cell_id")};
				";
				int nRow = _storedba.ExecuteNonquery(strQuery);

				blRet = true;
			}
			catch (MySqlException ex)
			{
				Console.WriteLine($"创建Cell失败： {ex.Message}");
				blRet = false;
			}

			return blRet;
		}

		public bool InsertCellPointers(Int64 nCellID, string strFieldNames, string[] Pointers)
		{
			bool blRet = false;

			StringBuilder sbQuery = new StringBuilder();
			sbQuery.Append($"INSERT INTO tblCell_Pointer({strFieldNames}) VALUES");

			int n = 0;
			foreach (var item in Pointers)
			{
				++n;
				sbQuery.Append($"({item})");
				if (n == Pointers.Length)
					sbQuery.Append(";");
				else
					sbQuery.Append(",");
			}

			try
			{
				int nRet = _iotdba.ExecuteNonquery(sbQuery.ToString());

				blRet = nRet == n;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool InsertCellPointer(Dictionary<string, object> dctCellPointer)
		{
			bool blRet = false;

			List<string> lstFields = new List<string>();
			List<string> lstFields1 = new List<string>();
			StringBuilder sbQuery = new StringBuilder();

			List<MySqlParameter> para = new List<MySqlParameter>();
			foreach (var item in dctCellPointer)
			{
				lstFields.Add(item.Key);
				lstFields1.Add("@" + item.Key);
				para.Add(new MySqlParameter($"@{item.Key}", dctCellPointer[item.Key]));
			}

			string strFields = string.Join(",", lstFields.ToArray());
			string strFields1 = string.Join(",", lstFields1.ToArray());
			sbQuery.Append("INSERT INTO tblcell_pointer(").Append(strFields).Append(") VALUES (").Append(strFields1).Append(")");
			try
			{
				int nRet = _iotdba.ExecuteNonquery(sbQuery.ToString(), para.ToArray());

				blRet = nRet == 1;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool QueryAppPointers(Int64 instanceID, out DataTable? dtPointers)
		{
			bool blRet = false;
			dtPointers = null;

			try
			{
				MySqlParameter[] para = new MySqlParameter[]
				{
					new MySqlParameter("@app_id", instanceID),
				};

				dtPointers = _iotdba.ExecuteProcQuery("QueryAppPointers", para);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool CheckDbExist(string strDbName)
		{
			try
			{
				if (strDbName.Equals("lbiotstore", StringComparison.OrdinalIgnoreCase) == true)
				{
					return _storedba.CheckDbExist(strDbName);
				}
				else if (strDbName.Equals("lbuserdba", StringComparison.OrdinalIgnoreCase) == true)
				{
					return _userdba.CheckDbExist(strDbName);
				}
				else if (strDbName.Equals("lbiot", StringComparison.OrdinalIgnoreCase) == true)
				{
					return _iotdba.CheckDbExist(strDbName);
				}
			}
			catch(DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			throw new InvalidOperationException($"unknown database name: {strDbName}");
		}

		public bool CreateDatabase(string strDbName)
		{
			try
			{
				if (strDbName.Equals("lbiotstore", StringComparison.OrdinalIgnoreCase) == true)
				{
					return _storedba.CreateDatabase(strDbName);
				}
				else if (strDbName.Equals("lbuserdba", StringComparison.OrdinalIgnoreCase) == true)
				{
					return _userdba.CreateDatabase(strDbName);
				}
				else if (strDbName.Equals("lbiot", StringComparison.OrdinalIgnoreCase) == true)
				{
					return _iotdba.CreateDatabase(strDbName);
				}
			}
			catch(DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			throw new InvalidOperationException($"unknown database name: {strDbName}");
		}

		public bool StoreQueryStoreList(out DataTable? dtStores)
		{
			bool blRet = false;
			dtStores = null;

			try
			{
				string strQuery = $"SELECT store_id, store_uuid, name, description FROM tblStore;";
				dtStores = _storedba.ExecuteDataTable(strQuery);

				blRet = true;
			}
			catch(DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool StoreQueryCellList(Int64 store_id, out DataTable? dtCells)
		{
			bool blRet = false;
			dtCells = null;

			try
			{
				string strQuery = $"SELECT cell_id, cell_uuid, type FROM tblCell_Info WHERE store_id = '{store_id}';";
				dtCells = _storedba.ExecuteDataTable(strQuery);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}
		public bool StoreQueryCellPointers(Int64 cellID, out DataTable? dtPointers)
		{
			bool blRet = false;
			dtPointers = null;

			StringBuilder sbQuery = new StringBuilder();
			sbQuery.Append($"SELECT * FROM tblCell_Pointer WHERE cell_id = {cellID};");

			try
			{
				dtPointers = _storedba.ExecuteDataTable(sbQuery.ToString());

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool StoreQueryCellInfo(Int64 cellID, out DataTable? dtCellInfo)
		{
			bool blRet = false;
			dtCellInfo = null;

			try
			{
				string strQuery = $"SELECT * FROM tblCell_Info WHERE cell_id = '{cellID}';";
				dtCellInfo = _storedba.ExecuteDataTable(strQuery);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool CreateCellApp(Int64 cell_id, out DataTable? dtNewApp)
		{
			bool blRet = false;
			dtNewApp = null;

			try
			{
				MySqlParameter[] para = new MySqlParameter[]
				{
					new MySqlParameter("@cellid", cell_id),
				};

				dtNewApp = _iotdba.ExecuteProcQuery("InsertApp", para);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		/// <summary>
		/// create cell app with cell id
		/// </summary>
		/// <param name="cell_id">cell id</param>
		/// <param name="nCount">count</param>
		/// <param name="app_ids">app id list</param>
		/// <param name="errMsg">error message when failed</param>
		/// <returns>success to true or failed to false</returns>
		public bool CreateCellApps(long cell_id, int nCount, bool blErrBreak, out DataTable? dtNewApps, out string? errMsg)
		{
			bool blRet = false;
			errMsg = null;
			dtNewApps = null;

			try
			{
				MySqlParameter[] para = new MySqlParameter[]
				{
					new MySqlParameter("@cell_id", cell_id),
					new MySqlParameter("@count", nCount),
					new MySqlParameter("@errbreak", blErrBreak),
					new MySqlParameter("@err_msg", errMsg),
				};
				para[3].Direction = ParameterDirection.Output;

				dtNewApps = _iotdba.ExecuteProcQuery("CreateCellApps", para);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		//public bool RemoveCellApp(long app_id, out string? errMsg = "")
		//{
		//	bool blRet = false;
		//	dtResult = null;

		//	try
		//	{
		//		MySqlParameter[] para = new MySqlParameter[]
		//		{
		//		new MySqlParameter("@app_id", app_id),
		//		new MySqlParameter("@err_msg", errMsg),
		//		};
		//		para[1].Direction = ParameterDirection.Output;

		//		_iotdba.ExecuteProcNonQuery("RemoveAppWithAppID", para);

		//		blRet = true;
		//	}
		//	catch (DbException ex)
		//	{
		//		Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
		//	}

		//	return blRet;
		//}

		public bool StoreDeleteCellType(Int64 cell_id)
		{
			bool blRet = false;

			try
			{
				MySqlParameter[] para = new MySqlParameter[]
				{
					new MySqlParameter("@cellid", cell_id),
				};

				_storedba.ExecuteProcNonQuery("DeleteCellType", para);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool QueryCellAppList(out DataSet? dsAppInfos, bool blEnable = true)
		{
			dsAppInfos = null;
			bool blRet = false;

			string strQuery;
			if (blEnable == true)
				strQuery = $"SELECT ai.cell_id, ai.app_id, ai.app_name FROM `tblapp_info` as ai WHERE ai.enable = 1;";
			else
				strQuery = $"SELECT ai.cell_id, ai.app_id, ai.app_name FROM `tblapp_info` as ai WHERE ai.enable = 0;";

			try
			{
				dsAppInfos = _iotdba.ExecuteDataSet(strQuery);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool QueryCellAppList(string strCellType, out DataSet? dsAppInfos, bool blEnable = true)
		{
			dsAppInfos = null;
			bool blRet = false;

			string strQuery;
			if (blEnable == true)
				strQuery = $"SELECT ai.app_id FROM `tblapp_info` as ai WHERE ai.enable = 1 AND ai.cell_id in (SELECT ci.cell_id FROM `tblcell_info` as ci WHERE '{strCellType}' = ci.type);";
			else
				strQuery = $"SELECT ai.app_id FROM `tblapp_info` as ai WHERE ai.enable = 0 AND ai.cell_id in (SELECT ci.cell_id FROM `tblcell_info` as ci WHERE '{strCellType}' = ci.type);";

			try
			{
				dsAppInfos = _iotdba.ExecuteDataSet(strQuery);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool QueryCellAppList(Int64 nCellID, out DataSet? dsAppInfos, bool blEnable = true)
		{
			dsAppInfos = null;
			bool blRet = false;

			string strQuery;
			if (blEnable == true)
				strQuery = $"SELECT ai.app_id FROM `tblapp_info` as ai WHERE ai.enable = 1 AND ai.cell_id = {nCellID};";
			else
				strQuery = $"SELECT ai.app_id FROM `tblapp_info` as ai WHERE ai.enable = 0 AND ai.cell_id = {nCellID};";

			try
			{
				dsAppInfos = _iotdba.ExecuteDataSet(strQuery);

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}

		public bool GetCellSummaryInfo(out DataTable? dtCellSummary)
		{
			bool blRet = false;
			dtCellSummary = null;

			try
			{
				dtCellSummary = _iotdba.ExecuteProcQuery("GetCellSummaryInfo");

				blRet = true;
			}
			catch (DbException ex)
			{
				Console.WriteLine($"Database exception occurred: {ex.Message} :\n{ex.StackTrace}");
			}

			return blRet;
		}
	}
}
